﻿/*===============================================================================
//©2009 Microsoft Corporation.  All rights reserved.
Deployment script for BitDB
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO

USE BitDB

GO

GO


GO
PRINT N'Creating db_BitDBexecutor...';


GO
CREATE ROLE [db_BitDBexecutor]
    AUTHORIZATION [dbo];

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO [db_BitDBexecutor]

GO

GO
PRINT N'Creating dbo.BitLockerRecoveryInformation...';


GO
CREATE TABLE [dbo].[BitLockerRecoveryInformation] (
    [ID]               INT              IDENTITY (1, 1) NOT NULL,
    [USNNumber]        INT              NULL,
    [MachineName]      VARCHAR (100)    NULL,
    [Owner]            VARCHAR (50)     NOT NULL,
    [RecoveryPassword] VARBINARY (400)  NOT NULL,
    [RecoveryGUID]     UNIQUEIDENTIFIER NOT NULL,
    [VolumeGUID]       UNIQUEIDENTIFIER NOT NULL,
    [KeyPackage]       VARBINARY (400)  NOT NULL,
    [TPMHash]          VARBINARY (400)  NULL,
    [TimeStamp]        DATETIME         NOT NULL
);


GO
PRINT N'Creating dbo.ConfigData...';


GO
CREATE TABLE [dbo].[ConfigData] (
    [ConfigID] INT          IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR (50) NULL,
    [Value]    VARCHAR (50) NULL
);


GO
PRINT N'Creating dbo.RecoveryRetrievalLookup...';


GO
CREATE TABLE [dbo].[RecoveryRetrievalLookup] (
    [RecoveryRetrievalID] INT IDENTITY (1, 1) NOT NULL,
    [BitLockerRecoveryID] INT NOT NULL,
    [KeyRetrievalID]      INT NOT NULL
);


GO
PRINT N'Creating dbo.ActivityLog...';


GO
CREATE TABLE [dbo].[ActivityLog] (
    [EventID]     INT           IDENTITY (1, 1) NOT NULL,
    [EventType]   VARCHAR (20)  NULL,
    [Description] VARCHAR (500) NULL,
    [TimeStamp]   DATETIME      NULL
);


GO
PRINT N'Creating dbo.KeyRetrievalLog...';


GO
CREATE TABLE [dbo].[KeyRetrievalLog] (
    [RetrievalID]           INT           IDENTITY (1, 1) NOT NULL,
    [RetrieverAlias]        VARCHAR (50)  NOT NULL,
    [RetrieverUserRoleID]   SMALLINT      NOT NULL,
    [KeySentToAlias]        VARCHAR (50)  NULL,
    [InputSearchParamenter] VARCHAR (100) NOT NULL,
    [KeyFound]              BIT           NOT NULL,
    [TimeStamp]             DATETIME      NOT NULL
);


GO
PRINT N'Creating dbo.Notification...';


GO
CREATE TABLE [dbo].[Notification] (
    [NotificationID]   SMALLINT      IDENTITY (1, 1) NOT NULL,
    [NotificationType] VARCHAR (50)  NOT NULL,
    [FromAlias]        VARCHAR (8)   NULL,
    [ToAlias]          VARCHAR (8)   NULL,
    [Message]          VARCHAR (MAX) NOT NULL,
    [Purpose]          VARCHAR (200) NOT NULL
);


GO
PRINT N'Creating dbo.SGRole...';


GO
CREATE TABLE [dbo].[SGRole] (
    [SGRoleID] SMALLINT      IDENTITY (1, 1) NOT NULL,
    [SGRole]   VARCHAR (50)  NOT NULL,
    [SGName]   VARCHAR (250) NULL
);


GO
PRINT N'Creating dbo.FAQ...';


GO
CREATE TABLE [dbo].[FAQ] (
    [FAQID]       INT           IDENTITY (1, 1) NOT NULL,
    [FAQParentID] INT           NULL,
    [FAQText]     VARCHAR (MAX) NULL,
    [SortOrder]   INT           NULL
);


GO
PRINT N'Creating dbo.Domains...';


GO
CREATE TABLE [dbo].[Domains] (
    [ID]             INT           IDENTITY (1, 1) NOT NULL,
    [Domain]         VARCHAR (30)  NOT NULL,
    [FQDN]           VARCHAR (100) NOT NULL,
    [ADServer]       VARCHAR (50)  NULL,
    [ScanSubDomains] INT           NULL
);


GO
PRINT N'Creating dbo.BitLockerRecoveryInformation._dta_index_BitLockerRecoveryInformation_5_21575115__K11D_K3_K5_1_4_6_7_8_9_10...';


GO
CREATE NONCLUSTERED INDEX [_dta_index_BitLockerRecoveryInformation_5_21575115__K11D_K3_K5_1_4_6_7_8_9_10]
    ON [dbo].[BitLockerRecoveryInformation]([TimeStamp] DESC, [MachineName] ASC)
    INCLUDE([ID], [Owner], [RecoveryPassword], [RecoveryGUID], [VolumeGUID], [KeyPackage], [TPMHash]) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);


GO
PRINT N'Creating dbo.BitLockerRecoveryInformation._dta_index_BitLockerRecoveryInformation_5_21575115__K3_K7_1...';


GO
CREATE NONCLUSTERED INDEX [_dta_index_BitLockerRecoveryInformation_5_21575115__K3_K7_1]
    ON [dbo].[BitLockerRecoveryInformation]([MachineName] ASC, [RecoveryGUID] ASC)
    INCLUDE([ID]) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);


GO
PRINT N'Creating dbo.PK_RecoveryRetrievalLookup...';


GO
ALTER TABLE [dbo].[RecoveryRetrievalLookup]
    ADD CONSTRAINT [PK_RecoveryRetrievalLookup] PRIMARY KEY CLUSTERED ([RecoveryRetrievalID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.PK_KeyRetrievalLog...';


GO
ALTER TABLE [dbo].[KeyRetrievalLog]
    ADD CONSTRAINT [PK_KeyRetrievalLog] PRIMARY KEY CLUSTERED ([RetrievalID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.PK_Notification...';


GO
ALTER TABLE [dbo].[Notification]
    ADD CONSTRAINT [PK_Notification] PRIMARY KEY CLUSTERED ([NotificationID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.PK_UserRole_1...';


GO
ALTER TABLE [dbo].[SGRole]
    ADD CONSTRAINT [PK_UserRole_1] PRIMARY KEY CLUSTERED ([SGRoleID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.PK_FAQ...';


GO
ALTER TABLE [dbo].[FAQ]
    ADD CONSTRAINT [PK_FAQ] PRIMARY KEY CLUSTERED ([FAQID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.PK_Domains...';


GO
ALTER TABLE [dbo].[Domains]
    ADD CONSTRAINT [PK_Domains] PRIMARY KEY CLUSTERED ([FQDN] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.BitLockerRecoveryInformation._dta_stat_21575115_5_4_3...';


GO
CREATE STATISTICS [_dta_stat_21575115_5_4_3]
    ON [dbo].[BitLockerRecoveryInformation]([Owner], [MachineName]);


GO
PRINT N'Creating dbo.BitLockerRecoveryInformation._dta_stat_21575115_3_5_7...';


GO
CREATE STATISTICS [_dta_stat_21575115_3_5_7]
    ON [dbo].[BitLockerRecoveryInformation]([MachineName], [RecoveryGUID]);


GO
PRINT N'Creating dbo.BitLockerRecoveryInformation._dta_stat_21575115_7_4_5_3...';


GO
CREATE STATISTICS [_dta_stat_21575115_7_4_5_3]
    ON [dbo].[BitLockerRecoveryInformation]([RecoveryGUID], [Owner], [MachineName]);


GO
PRINT N'Creating dbo.BitLockerRecoveryInformation._dta_stat_21575115_11_3_5_7_4...';


GO
CREATE STATISTICS [_dta_stat_21575115_11_3_5_7_4]
    ON [dbo].[BitLockerRecoveryInformation]([TimeStamp], [MachineName], [RecoveryGUID], [Owner]);


GO
PRINT N'Creating dbo.FK_KeyRetrievalLog_SGRole...';


GO
ALTER TABLE [dbo].[KeyRetrievalLog]
    ADD CONSTRAINT [FK_KeyRetrievalLog_SGRole] FOREIGN KEY ([RetrieverUserRoleID]) REFERENCES [dbo].[SGRole] ([SGRoleID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating dbo.usp_GetBitLockerRecoveryInfoAdmin...';


GO
CREATE PROCEDURE [dbo].[usp_GetBitLockerRecoveryInfoAdmin]
@SearchParameter VARCHAR (50), @RequestedFor VARCHAR (50), @RetrieverAlias VARCHAR (50), @RetrieverRoleID INT
AS
BEGIN
	SET NOCOUNT ON;


	DECLARE  @Found bit,@KeyRetrievalID int, @BitlockerRecoveryID int
	SET @BitlockerRecoveryID = 0
	SET @Found = 'false'

	Select ID, MachineName, [Owner],RecoveryGUID, RecoveryPassword, 
	KeyPackage, TPMHash, VolumeGUID, [TimeStamp] From BitLockerRecoveryInformation 
	WHERE (Owner LIKE '%' + @SearchParameter OR 
	MachineName = @SearchParameter OR RecoveryGUID LIKE @SearchParameter +'%')
	ORDER BY [TimeStamp] DESC
	
	
	IF @@ROWCOUNT <> 0
		BEGIN
			SET @Found = 'true'	
		END


	INSERT INTO KeyRetrievalLog ([RetrieverAlias]
		  ,[RetrieverUserRoleID]
		  ,[KeySentToAlias]
		  ,[InputSearchParamenter]
		  ,[KeyFound]
		  ,[TimeStamp])
	VALUES (@RetrieverAlias,@RetrieverRoleID, @RequestedFor,
	'Search parameter:' + @SearchParameter,
	@Found,GetDate())

END


GO
PRINT N'Creating dbo.usp_GetActivitySummaryReport...';


GO
CREATE PROCEDURE [dbo].[usp_GetActivitySummaryReport]
@StartDate DATETIME, @EndDate DATETIME
AS
BEGIN
	SET NOCOUNT ON;
	SELECT (SELECT COUNT(RetrievalID) FROM KeyRetrievalLog WHERE RetrieverUserRoleID = '1' AND [TimeStamp] BETWEEN @StartDate AND @EndDate AND KeyFound='true') AS UserKeyFound
	,(SELECT COUNT(RetrievalID) FROM KeyRetrievalLog WHERE RetrieverUserRoleID = '1' AND [TimeStamp] BETWEEN @StartDate AND @EndDate AND KeyFound='false') AS UserKeyNotFound
	,(SELECT COUNT(RetrievalID) FROM KeyRetrievalLog WHERE RetrieverUserRoleID ='2' AND [TimeStamp] BETWEEN @StartDate AND @EndDate AND KeyFound='true') AS HSTKeyFound
	,(SELECT COUNT(RetrievalID) FROM KeyRetrievalLog WHERE RetrieverUserRoleID ='2' AND [TimeStamp] BETWEEN @StartDate AND @EndDate AND KeyFound='false') AS HSTKeyNotFound
	,(SELECT COUNT(RetrievalID) FROM KeyRetrievalLog WHERE RetrieverUserRoleID ='3' AND [TimeStamp] BETWEEN @StartDate AND @EndDate AND KeyFound='true') AS AdminKeyFound
	,(SELECT COUNT(RetrievalID) FROM KeyRetrievalLog WHERE RetrieverUserRoleID ='3' AND [TimeStamp] BETWEEN @StartDate AND @EndDate AND KeyFound='false') AS AdminKeyNotFound
	,(SELECT COUNT(RetrievalID) FROM KeyRetrievalLog WHERE [TimeStamp] BETWEEN @StartDate AND @EndDate) AS TotalKeys
	,(SELECT COUNT(ID) FROM [BitLockerRecoveryInformation]) AS TotalAllKeys
	

END


GO
PRINT N'Creating dbo.usp_GetActivityDetailsReport...';


GO
CREATE PROCEDURE [dbo].[usp_GetActivityDetailsReport]
@StartDate DATETIME, @EndDate DATETIME
AS
BEGIN
	SET NOCOUNT ON;
	SELECT RetrieverAlias, MachineName, KeySentToAlias, [Owner],InputSearchParamenter, KeyFound, [TimeStamp], SGRole
	FROM RecoveryRetrievalView
	WHERE [TimeStamp] BETWEEN @StartDate AND @EndDate
	

END


GO
PRINT N'Creating dbo.usp_UpdateFAQ...';


GO
CREATE PROCEDURE [dbo].[usp_UpdateFAQ]
@FAQID INT, @SortOrder INT, @FAQText VARCHAR (MAX)
AS
BEGIN

	SET NOCOUNT ON;

UPDATE [BitDB].[dbo].[FAQ]
   SET [FAQText] = @FAQText
      ,[SortOrder] = @SortOrder
WHERE FAQID = @FAQID

END


GO
PRINT N'Creating dbo.usp_DeleteFAQ...';


GO
CREATE PROCEDURE [dbo].[usp_DeleteFAQ]
@FAQID INT
AS
BEGIN

	SET NOCOUNT ON;

DELETE FROM [BitDB].[dbo].[FAQ]
      WHERE FAQID = @FAQID

END


GO
PRINT N'Creating dbo.usp_InsertFAQ...';


GO
CREATE PROCEDURE [dbo].[usp_InsertFAQ]
@SortOrder INT, @FAQText VARCHAR (MAX)
AS
BEGIN

	SET NOCOUNT ON;

INSERT INTO [BitDB].[dbo].[FAQ]
           ([FAQText]
           ,[SortOrder])
     VALUES
           (@FAQText,
           @SortOrder)

END


GO
PRINT N'Creating dbo.usp_UpdateConfigData...';


GO
CREATE PROCEDURE [dbo].[usp_UpdateConfigData]
@ConfigName VARCHAR (50), @ConfigValue VARCHAR (50)
AS
BEGIN

	SET NOCOUNT ON;

UPDATE [BitDB].[dbo].[ConfigData]
   SET [Value] = @ConfigValue
WHERE [Name] = @ConfigName

END


GO
PRINT N'Creating dbo.usp_GetEmailTemplateList...';


GO
CREATE PROCEDURE [dbo].[usp_GetEmailTemplateList]

AS
BEGIN
	SET NOCOUNT ON;

SELECT [NotificationID]
      ,[NotificationType]
      ,[FromAlias]
      ,[ToAlias]
      ,[Message]
      ,[Purpose]
  FROM [BitDB].[dbo].[Notification]

END


GO
PRINT N'Creating dbo.usp_UpdateEmailTemplate...';


GO
CREATE PROCEDURE [dbo].[usp_UpdateEmailTemplate]
@NotifID INT, @From VARCHAR (8), @Subject VARCHAR (200), @Message VARCHAR (MAX)
AS
BEGIN

	SET NOCOUNT ON;

UPDATE [BitDB].[dbo].[Notification]
   SET [FromAlias] = @From,
[Purpose]=@Subject,
[Message]=@Message
WHERE [NotificationID] = @NotifID

END


GO
PRINT N'Creating dbo.usp_SetScanSubDomainsFlag...';


GO
CREATE PROCEDURE [dbo].[usp_SetScanSubDomainsFlag]
@FQDN VARCHAR (100), @ScanSubDomains INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   Update dbo.Domains set ScanSubDomains = @ScanSubDomains where FQDN=@FQDN

END


GO
PRINT N'Creating dbo.usp_InsertNewDomains...';


GO
CREATE PROCEDURE [dbo].[usp_InsertNewDomains]
@Domain VARCHAR (30), @FQDN VARCHAR (100), @ADServer VARCHAR (50), @ScanSubDomains INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   insert into dbo.Domains values (@Domain,@FQDN,@ADServer,@ScanSubDomains)

END


GO

PRINT N'Creating dbo.usp_GetLogFlag...';


GO
CREATE PROCEDURE [dbo].[usp_GetLogFlag]
@Name VARCHAR (50)
AS
BEGIN

	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT Value from dbo.ConfigData where Name =@Name
END


GO
PRINT N'Creating dbo.usp_UpdateBitLockerRecoveryInformation...';


GO
CREATE PROCEDURE [dbo].[usp_UpdateBitLockerRecoveryInformation]
@Owner VARCHAR (50), @RecoveryPassword VARBINARY (400), @RecoveryGUID UNIQUEIDENTIFIER, @VolumeGUID UNIQUEIDENTIFIER, @KeyPackage VARBINARY (400), @TPMHash VARBINARY (400), @MachineName VARCHAR (100), @USNNumber INT
AS
BEGIN
	SET NOCOUNT ON;

  
	if exists(select RecoveryGUID from BitLockerRecoveryInformation where RecoveryGUID = @RecoveryGUID)
	begin 
		Update BitLockerRecoveryInformation set USNNumber =@USNNumber, MachineName = @MachineName, Owner = @Owner,
			RecoveryPassword = 	@RecoveryPassword,
			 RecoveryGUID = @RecoveryGUID, VolumeGUID = @VolumeGUID,
			KeyPackage =  @KeyPackage,
			TPMHash = @TPMHash  , TimeStamp = getdate() where RecoveryGUID = @RecoveryGUID
			AND USNNumber <> @USNNumber
	end
	else
	begin		
		Insert into BitLockerRecoveryInformation values (@USNNumber,@MachineName, @Owner,
			@RecoveryPassword,@RecoveryGUID,@VolumeGUID,@KeyPackage,
				@TPMHash,getdate())
	end

END


GO
PRINT N'Creating dbo.usp_GetConfigData...';


GO
CREATE PROCEDURE [dbo].[usp_GetConfigData]
@ConfigName VARCHAR (50)
AS
BEGIN

	SET NOCOUNT ON;

    -- Insert statements for procedure here
		SELECT Value from dbo.ConfigData (nolock) where Name = @ConfigName

END


GO
PRINT N'Creating dbo.usp_UpdateLogs...';


GO
CREATE PROCEDURE [dbo].[usp_UpdateLogs]
@EventType VARCHAR (20), @Description VARCHAR (500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   insert into dbo.ActivityLog values (@EventType,@Description,getdate())

END


GO
PRINT N'Creating dbo.usp_GetDomains...';


GO
CREATE PROCEDURE [dbo].[usp_GetDomains]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   --select * from dbo.Domains
	Select FQDN from Domains

END


GO
PRINT N'Creating dbo.usp_GetUSNNumber...';


GO
CREATE PROCEDURE [dbo].[usp_GetUSNNumber]

AS
BEGIN

	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT value from dbo.ConfigData where name = 'USNNumber'
END


GO
PRINT N'Creating dbo.usp_UpdateUSNNumber...';


GO
CREATE PROCEDURE [dbo].[usp_UpdateUSNNumber]
@USNNumber INT
AS
BEGIN

SET NOCOUNT ON;

    -- Insert statements for procedure here
	Update ConfigData set value = @USNNumber where name = 'USNNumber'

	-- Cleanup ActivityLog
	DELETE FROM [BitDB].[dbo].[ActivityLog]
    WHERE TimeStamp <= DATEADD(dd,-14,SYSDATETIME())

END


GO
PRINT N'Creating dbo.usp_UpdateLastLoggedUser...';


GO

PRINT N'Creating dbo.usp_GetEmailInfoAdmin...';


GO
CREATE PROCEDURE [dbo].[usp_GetEmailInfoAdmin]

AS
BEGIN
	SET NOCOUNT ON;

	SELECT NotificationID, [NotificationType], FromAlias,[Message],[Purpose] FROM [Notification]


END


GO
PRINT N'Creating dbo.usp_GetEmailInformation...';


GO
CREATE PROCEDURE [dbo].[usp_GetEmailInformation]
@NotificationType VARCHAR (50)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT FromAlias,[Message],[Purpose] FROM [Notification] WHERE [NotificationType]=@NotificationType


END


GO

PRINT N'Creating dbo.usp_GetSecurityGroupByID...';


GO
CREATE PROCEDURE [dbo].[usp_GetSecurityGroupByID]
@RoleID INT
AS
BEGIN
	SET NOCOUNT ON;
	SELECT SGRole, SGName FROM SGRole WHERE SGRoleID=@RoleID

END


GO
PRINT N'Creating dbo.usp_GetFAQ...';


GO
CREATE PROCEDURE [dbo].[usp_GetFAQ]

AS
BEGIN

	SET NOCOUNT ON;

    -- Insert statements for procedure here
		SELECT FAQID, FAQText, SortOrder from FAQ ORDER BY SortOrder

END


GO
PRINT N'Creating dbo.usp_GetBitLockerRecoveryInfoByIDAlias...';


GO
CREATE PROCEDURE [dbo].[usp_GetBitLockerRecoveryInfoByIDAlias]
@RecoverypasswordID VARCHAR (8)=NULL, @RequestedFor VARCHAR (50), @RetrieverAlias VARCHAR (50), @RetrieverRoleID INT=1
AS
BEGIN
	SET NOCOUNT ON;


	DECLARE  @Found bit, @IDcheck varchar(8), @KeyRetrievalID int, @BitlockerRecoveryID int

	SET @Found = 'false'
	SET @IDCheck = ISNull(@RecoverypasswordID,'')

	Select ID, MachineName,RecoveryGUID, RecoveryPassword, 
	KeyPackage, TPMHash, VolumeGUID,[Owner] From BitLockerRecoveryInformation 
	WHERE RecoveryGUID LIKE @IDCheck + '%'
	AND [Owner] LIKE @RequestedFor 


	IF @@ROWCOUNT <> 0
		BEGIN
			SET @Found = 'true'	
		END


	INSERT INTO KeyRetrievalLog ([RetrieverAlias]
		  ,[RetrieverUserRoleID]
		  ,[KeySentToAlias]
		  ,[InputSearchParamenter]
		  ,[KeyFound]
		  ,[TimeStamp])
	VALUES (@RetrieverAlias,@RetrieverRoleID, @RequestedFor,
	'Recovery Password ID:' + @IDCheck + ' Requested For: ' + @RequestedFor,
	@Found,GetDate())

END


GO
PRINT N'Creating dbo.KeyRetreivalReport...';


GO
CREATE VIEW [dbo].[KeyRetreivalReport]
AS
SELECT     dbo.KeyRetrievalLog.RetrieverAlias, dbo.SGRole.SGRole, dbo.KeyRetrievalLog.KeySentToAlias, dbo.KeyRetrievalLog.InputSearchParamenter, 
                      dbo.KeyRetrievalLog.KeyFound, dbo.KeyRetrievalLog.TimeStamp
FROM         dbo.KeyRetrievalLog LEFT OUTER JOIN
                      dbo.SGRole ON dbo.KeyRetrievalLog.RetrieverUserRoleID = dbo.SGRole.SGRoleID;


GO
PRINT N'Creating dbo.RecoveryRetrievalView...';


GO
CREATE VIEW [dbo].[RecoveryRetrievalView]
AS
SELECT     dbo.KeyRetrievalLog.RetrieverAlias, dbo.BitLockerRecoveryInformation.MachineName, dbo.KeyRetrievalLog.KeySentToAlias, 
                      dbo.BitLockerRecoveryInformation.Owner, dbo.KeyRetrievalLog.InputSearchParamenter, 
                      dbo.KeyRetrievalLog.KeyFound, dbo.KeyRetrievalLog.TimeStamp, dbo.SGRole.SGRole
FROM         dbo.KeyRetrievalLog 
LEFT OUTER JOIN dbo.RecoveryRetrievalLookup ON dbo.KeyRetrievalLog.RetrievalID = dbo.RecoveryRetrievalLookup.KeyRetrievalID 
LEFT OUTER JOIN dbo.BitLockerRecoveryInformation ON dbo.RecoveryRetrievalLookup.BitLockerRecoveryID = dbo.BitLockerRecoveryInformation.ID 
LEFT OUTER JOIN dbo.SGRole ON dbo.KeyRetrievalLog.RetrieverUserRoleID = dbo.SGRole.SGRoleID;


GO

GO

GO
